package com.bigData.main.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface CarCountMapper {
    @Select("SELECT cname, color, COUNT(*) AS quantity  FROM  car  GROUP BY cname, color ORDER BY quantity DESC ")
    List<Map<String, Object>> countCar();

    //车辆颜色
    @Select("SELECT color, COUNT(*) as count FROM car GROUP BY color")
    List<Map<String, Object>> countColor();

    //价格区间
    @Select("SELECT  " +
            "        CASE " +
            "            WHEN price < 200000 THEN '20万以下' " +
            "            WHEN price BETWEEN 200000 AND 300000 THEN '20-30万' " +
            "            WHEN price BETWEEN 300000 AND 400000 THEN '30-40万' " +
            "            WHEN price BETWEEN 400000 AND 500000 THEN '40-50万' " +
            "            WHEN price BETWEEN 500000 AND 600000 THEN '50-60万' " +
            "            WHEN price > 700000 THEN '70万以上' " +
            "        END AS price_range, " +
            "        COUNT(*) AS count  " +
            "    FROM  car" +
            "    GROUP BY price_range " +
            "    ORDER BY MIN(price) ")
    List<Map<String, Object>> countPriceRange();

    //月份统计
    @Select(" SELECT DATE_FORMAT(time, '%Y-%m') AS month, COUNT(*) AS sales FROM  car WHERE status = '已售出' GROUP BY month ORDER BY month ASC ")
    List<Map<String, Object>> countMonthlySales();

    //已售车辆占自身品牌的比重
    @Select("SELECT  " +
            "    cname AS brand, " +
            "    (SELECT COUNT(*) FROM  car WHERE STATUS = '已售出') AS total,   " +
            "    COUNT(*) AS sold   " +
            "FROM  car " +
            "WHERE  STATUS = '已售出'   " +
            "GROUP BY    cname " +
            "ORDER BY  sold DESC ")
    List<Map<String, Object>> countBrandRatio();

    //统计已卖出的车辆总和
    @Select("SELECT COUNT(*) FROM car WHERE status = '未售出';")
    Integer unAllCar();

    //统计员工数
    @Select("SELECT COUNT(*)   FROM  user_info  WHERE uroot != 8;")
    Integer allUser();

    //仓库个数
    @Select("SELECT COUNT(DISTINCT STORAGE) AS storage_kinds_count  FROM  car")
    Integer allStorage();

    //各个仓库个数
    @Select("SELECT    storage AS total_quantity,  COUNT(*) AS count " +
            "FROM     car " +
            "GROUP BY  storage;")
    List<Map<String, Object>> allStorageCount();

    //统计个人账单数
    @Select("SELECT creator AS total_quantity " +
            "FROM  car " +
            "GROUP BY creator;")
    List<Map<String, Object>> countUserQuantity();

    /**
     * 统计所有收益数
     * <p>
     * creator_count_status_1 账单完成数
     * total_creator_count  账单总数
     * creator_count_status_1_percentage  账单完成率
     * total_quantity_status_1  车辆售出数
     * total_value_status_1  已完成收益
     *
     * @return
     */
    @Select("SELECT  " +
            "    u.uid,  " +
            "    c.creator,  " +
            "    COUNT(CASE WHEN c.status = '已售出' THEN 1 END) AS creator_count_status_1,   " +
            "    COUNT(*) AS total_creator_count,   " +
            "    (COUNT(CASE WHEN c.status = '已售出' THEN 1 END) * 100.0 / COUNT(*)) AS creator_count_status_1_percentage,   " +
            "    COUNT(CASE WHEN c.status = '已售出' THEN 1 END) AS total_quantity_status_1,  " +
            "    SUM(CASE WHEN c.status = '已售出' THEN c.price ELSE 0 END) AS total_value_status_1   " +
            "FROM  car c " +
            "JOIN   user_info u  " +
            "ON   c.creator = u.uname " +
            "GROUP BY  u.uid, c.creator " +
            "ORDER BY   total_value_status_1 DESC;")
    List<Map<String, Object>> countUserPrice();

    // 计算收入
    @Select("SELECT SUM(price) AS total FROM  car WHERE status = '已售出' AND time BETWEEN #{startTime} AND #{endTime}")
    Map<String, Object> calculateRevenue(@Param("startTime") String startTime, @Param("endTime") String endTime);

    // 计算支出
    @Select("SELECT SUM(price) AS total FROM  car WHERE status = '未售出' AND time BETWEEN #{startTime} AND #{endTime}")
    Map<String, Object> calculateExpenses(@Param("startTime") String startTime, @Param("endTime") String endTime);
}
